SQL: Structured Query Language

SQL is designed for RDBMS (relational database management system).

Table of Contents

On top of the schema definition learned in Entity-Relationship Model, we can use SQL to create, modify, query, and view our database.

1. Data Definition

1.1. Create Table

We can use CREATE TABLE grammar.

CREATE TABLE Owner
(
  account_id VARCHAR(15),
  customer_id VARCHAR(15) NOT NULL,
  PRIMARY KEY (account_id, customer_id),
  FOREIGN KEY (account_id) REFERENCES Account(account_id),
  FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

Following the CREATE TABLE is the table name. Then all keys and corresponding attributes will be wrapped by paranthesis.

To declare a column, type field name followed by its type. By appending NOT NULL to indicate the column cannot be a null value.

PRIMARY KEY and FOREIGN KEY are constraints. PRIMARY KEY ensures NOT NULL.

1.2. Remove Table

Just simply drop it. But if this table is referenced via some constraints, the DBMS may reject the drop.

DROP TABLE Branch;

1.3. Modify Table

What it means by modifying the table, is to add or remove columns, or add constraints to the table, say, add a primary key or a foreign key.

-- Add column
ALTER TABLE Branch ADD branch_phone VARCHAR(20);

-- Remove a column
ALTER TABLE Branch DROP branch_phone;

-- Add constraints: add primary key
ALTER TABLE Branch ADD PRIMARY KEY (branch_id);

-- Add constraints: add foreign key
ALTER TABLE Branch ADD FOREIGN KEY (customer_id) REFERENCES Customer(customer_id);

2. Data Modification

2.1. Insert Records into a Table

We can manually insert columns.

INSERT INTO Branch VALUES
  (val_of_col1, val_of_col2, ...),
  (val_of_col1, val_of_col2, ...);

In MySQL, we can also use a file. Say, in the file text.txt, each record takes a whole line, columns are separated by ;, then we can write the follow command to auto insert this file into DBMS.

LOAD DATA LOCAL INFILE 'text.txt'
INTO TABLE Branch
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n';

2.2. Remove Records from Table

The idea if to use WHERE clause for conditioning.

DELETE FROM Branch WHERE field='Central';

2.3. Update Records

In the SET clause, we can also use arithmetics,

UPDATE Branch
SET asset = asset * 1.05
WHERE branch_id = 'B1';

as well as if-else,

UPDATE Account
SET balance = CASE
  WHEN balance < 500 THEN balance * 1.05
  ELSE balance * 1.06
  END;

3. Query

3.1. Basic Selecting

The SELECT clause lists the attributes desired in the results of a query. An asterisk denotes “all attributes”

SELECT age, religion from Customer;
SELECT * from Customer;

We can also perform arithmetics on attributes. The effect is equivalent to performing arithmetics to all values of that attribute.

SELECT load_id, amount/7.8 FROM Loan;

Conditioning. We may also use WHERE to condition, as well as AND, OR, NOT predicates.

-- For each loan, find out the name of the customer who borrowed the loan.
SELECT Borrower.load_id, Customer.name
FROM Customer, Borrower
WHERE Customer.customer_id = Borrower.customer_id;

3.2. Operations on Selection

Rename. We can use AS to signify the renamed attributes and the renamed tables

SELECT DISTINCT Branch.name AS 'Branch name'
FROM Branch, Loan
WHERE Branch.branch_id = Loan.branch_id;

SELECT DISTINCT B.name AS 'Branch name'
FROM Branch B, Loan L
WHERE B.branch_id = L.branch_id;

Pattern Matching. LIKE clause allows us to pattern matching: % matches any substring (can be null), _ matches any single character. They are case sensitive.

3.3. Functions on Selection

To order results, we can explicitly set ORDER BY, the following snippet sort names in alphabetic order, then amount in descending order.

SELECT name
FROM Customer
ORDER BY name ASC, amount DESC;
-- DESC for descending order.
-- ASC by default.
-- can have multiple sort keys

Aggregation functions take a collection of values as input and return a single value. For example, AVG, SUM, MIN, MAX, COUNT.

SELECT AVG(balance)
FROM Account
WHERE balance_id = 'B2';

SELECT branch_id, AVG(balance)
FROM Account
GROUP BY branch_id;

The HAVING clause states a condition that applies to groups.

SELECT branch_id, AVG(balance)   -- step 4
FROM Account                     -- step 1
GROUP BY branch_id;              -- step 2
HAVING AVG(balance) >= 650;      -- step 3

4. Nested Query

4.1. Nested Query Operator: IN

We can simply nest queries in (), connected by nested operators, e.g., IN(). IN() asks the value must appear in the set indicated by ().

SELECT E.name
FROM Employee E
WHERE E.employee_id IN (
    SELECT W.employee_id
    FROM Works_in W
    WHERE W.department_id = 1
);

4.2. Nested Query Operator: ALL

Operator ALL() asks the value of the column must satisfy all conditions.

-- Find the names of departments whose budget is greater than the budget of every department where employee 4 has worked.
SELECT D.name
FROM Department D
WHERE D.budget > ALL (
    SELECT D2.budget
    FROM Department D2
    WHERE D2.department_id IN (
        SELECT W.department_id
        FROM Works_in W
        WHERE W.employee_id = 4
    )
);

4.3. Nested Query Operator: SOME

Similar to ALL(), but only requires at least one satisfaction. If the nested query result is empty, the SOME() will return false.

4.4. Nested Query Operator: EXISTS

Tests whether a subquery returns at least one row.

5. Set Operations

5.1. Union, INTERSECT, EXCEPT

5.2. Merging Tables with JOIN

5.3. NULL values.

Date: 2026-09-29 Tue

Author: ArcaLunar